Data Source Classes

To create a data source class, you need to follow a few elementary steps. First add a reference to the Microsoft ActiveX Data Objects 2.0 (or 2.1) Library. Then set the DataSourceBehavior attribute of the class to 1-vbDataSource, which automatically adds a reference to the Microsoft Data Source Interfaces type library (Msdatsrc.tlb). You can now use a new GetDataMember event, the DataMembers property, and the DataMemberChanged method of the class. You can set the DataSourceBehavior attribute to the value 1-vbDataSource in Private classes in any type of project or in Public classes in ActiveX DLL projects, but you can't do that in ActiveX EXE projects because the data source interfaces can't work across processes. You can also create a data source class by selecting the appropriate template when you add a new class module to the current project: In this case, you'll get a class with some skeleton code already present, but you have to add a reference to the Msdatsrc.tlb library manually. You can also create a data source class using the Data Form Wizard.

The key to building a data source is the code that you write in the GetDataMember event. This event receives a DataMember argument—a string that identifies which particular member the data consumer is requesting—and a Data argument declared as Object. In the simplest case, you can ignore the first argument and return an object that supports the necessary ADO interfaces in the Data argument. You can return an ADO Recordset, another data source class, or an OLEDBSimpleProvider class that you've created elsewhere in the application (as described in the "OLE DB Simple Providers" section later in this chapter).

I've prepared a demonstration program that builds on an ArrayDataSource class, whose source code is on the companion CD. The purpose of this class is to let you browse the contents of a two-dimensional array of Variants using bound controls: You can load data into an array, pass the array to the SetArray method of the class, and then display its contents in a DataGrid or another data-aware control. The user can modify existing values, delete records, and even add new ones. When the editing is completed, the client code can call the class's GetArray method to retrieve the new contents of the array.

The ArrayDataSource class, like most data source classes, incorporates an ADO Recordset object. The SetArray method creates the Recordset, adds the fields whose names have been passed in the Fields array argument, and then fills the Recordset with the data contained in the Values array passed as an argument to the method:

Private rs As ADODB.Recordset ' Module-level variable Sub SetArray(Values As Variant, Fields As Variant) Dim row As Long, col As Long ' Build a new ADO Recordset. If Not (rs Is Nothing) Then If rs.Status = adStateOpen Then rs.Close End If Set rs = New ADODB.Recordset ' Create the Fields collection. For col = LBound(Fields) To UBound(Fields) rs.Fields.Append Fields(col), adBSTR Next ' Move data from the array to the Recordset. rs.Open For row = LBound(Values) To UBound(Values) rs.AddNew For col = 0 To UBound(Values, 2) rs(col) = Values(row, col) Next Next rs.MoveFirst ' Inform consumers that the data has changed. DataMemberChanged "" End Sub

The call to the DataMemberChanged method informs bound controls (more generally, data consumers) that a new data set is available. Both arguments to the SetArray method are declared as Variants, so you can pass them an array of any data type. After the Recordset has been created, it can be safely returned in the GetDataMember event. This event fires the first time a data consumer asks for data and whenever the DataMemberChanged method is called:

' Return the Recordset to the data consumer. Private Sub Class_GetDataMember(DataMember As String, Data As Object) Set Data = Recordset End Sub ' Provides "Safe" access to the Recordset, ' in that it raises a meaningful error if the Recordset is set to Nothing. Property Get Recordset() As ADODB.Recordset If rs Is Nothing Then Err.Raise 1001, , "No data array has been provided" Else Set Recordset = rs End If End Property

The event procedure references the Private rs variable through the Public Recordset property; this raises an error with a meaningful message instead of the standard "Object variable or With block variable not set" error message that would be raised if the client code assigns the data source to a bound control before calling the SetArray method. A data source class should also expose all the properties and methods that you expect from an ADO source, including all the navigational Movexxxx methods, the AddNew and Delete methods, the EOF and BOF properties, and so on. The following code simply delegates to the inner rs variable through the Recordset property, which ensures that proper error checking is performed:

' Partial listing of properties and methods Public Property Get EOF() As Boolean EOF = Recordset.EOF End Property Public Property Get BOF() As Boolean BOF = Recordset.BOF End Property Public Property Get RecordCount() As Long RecordCount = Recordset.RecordCount End Property Sub MoveFirst() Recordset.MoveFirst End Sub Sub MovePrevious() Recordset.MovePrevious End Sub ' And so on...

The code in the class needs to convert the data stored in the Recordset back into a Variant array when the client application requests it. This conversion occurs in the GetArray method:

Function GetArray() As Variant Dim numFields As Long, row As Long, col As Long Dim Bookmark As Variant ' Remember the current record pointer. Bookmark = Recordset.Bookmark ' Create the result array, and fill it with data from the Recordset. numFields = rs.Fields.Count ReDim Values(0 To rs.RecordCount - 1, 0 To numFields - 1) As String ' Fill the array with data from the Recordset. rs.MoveFirst For row = 0 To rs.RecordCount - 1 For col = 0 To numFields - 1 Values(row, col) = rs(col) Next rs.MoveNext Next GetArray = Values ' Restore the record pointer. rs.Bookmark = Bookmark End Function

The complete version of the class on the companion CD supports additional properties, including the BOFAction and EOFAction properties, which let the class behave similarly to a Data control. To test-drive the ArrayDataSource class, create a form with three TextBox controls and a set of navigational buttons, as shown in Figure 18-1. Then add this code in the Form_Load event procedure:

Dim MyData As New ArrayDataSource ' Module-level variable Private Sub Form_Load() ReDim Fields(0 To 2) As String ' Create the Fields array. Fields(0) = "ID" Fields(1) = "Name" Fields(2) = "Department" ReDim Values(0 To 3, 0 To 2) As String ' Create the Values array. Values(0, 0) = 100 ' ID field Values(0, 1) = "Christine Johnson" ' Name field Values(0, 2) = "Marketing" ' Department field ' Fill other records (omitted...) MyData.SetArray Values, Fields ' Initialize the data source. ' Bind the controls. Set txtID.DataSource = MyData txtID.DataField = "ID" Set txtName.DataSource = MyData txtName.DataField = "Name" Set txtDepartment.DataSource = MyData txtDepartment.DataField = "Department" End Sub

When the client program needs to retrieve the data edited by the user, it invokes the GetArray method:

Dim Values() As String Values = MyData.GetArray()

Figure 18-1. A client form to test-drive the ArrayDataSource class.

Support for the DataMember Property

The ArrayDataSource class is the simplest type of data source class that you can build with Visual Basic 6 and doesn't take into account the DataMember argument passed to the GetDataMember event. You can greatly enhance your class by adding support for the DataMember property in bound controls. All you have to do is build and return a different Recordset, depending on the DataMember you receive.

I've prepared a sample data source class, named FileTextDataSource, which binds its consumers to the fields of a semicolon-delimited text file. If you want to bind one or more controls to such a class, you must specify the name of the text file in the control's DataMember property:

' Code in the client form Dim MyData As New TextFileDataSource Private Sub Form_Load() ' This is the path for data files. MyData.FilePath = DB_PATH ' Bind the text controls. (Their DataField was set at design time.) Dim ctrl As Control For Each ctrl In Controls If TypeOf ctrl Is TextBox Then ctrl.DataMember = "Publishers" Set ctrl.DataSource = MyData End If Next End Sub

The TextFileDataSource class module contains more code than the simpler ArrayDataSource class does, but most of it is necessary just to parse the text file and move its contents into the private Recordset. The first line in the text file is assumed to be the semicolon-delimited list of field names:

Const DEFAULT_EXT = ".DAT" ' Default extension for text files Private rs As ADODB.Recordset Private m_DataMember As String, m_File As String, m_FilePath As String Private Sub Class_GetDataMember(DataMember As String, Data As Object) If DataMember = "" Then Exit Sub ' Re-create the Recordset only if necessary. If DataMember <> m_DataMember Or (rs Is Nothing) Then LoadRecordset DataMember End If Set Data = rs End Sub Private Sub LoadRecordset(ByVal DataMember As String) Dim File As String, fnum As Integer Dim row As Long, col As Long, Text As String Dim Lines() As String, Values() As String On Error GoTo ErrorHandler File = m_FilePath & DataMember If InStr(File, ".") = 0 Then File = File & DEFAULT_EXT ' Read the contents of the file in memory. fnum = FreeFile() Open File For Input As #fnum Text = Input$(LOF(fnum), #fnum) Close #fnum ' Close the current Recordset, and create a new one. CloseRecordset Set rs = New ADODB.Recordset ' Convert the long string into an array of records. Lines() = Split(Text, vbCrLf) ' Get the field names, and append them to the Fields collection. Values() = Split(Lines(0), ";") For col = 0 To UBound(Values) rs.Fields.Append Values(col), adBSTR Next ' Read the actual values, and append them to the Recordset. rs.Open For row = 1 To UBound(Lines) rs.AddNew Values() = Split(Lines(row), ";") For col = 0 To UBound(Values) rs(col) = Values(col) Next Next rs.MoveFirst ' Remember DataMember and File for the next time. m_DataMember = DataMember m_File = File Exit Sub ErrorHandler: Err.Raise 1001, , "Unable to load data from " & DataMember End Sub ' If the Recordset is still open, close it. Private Sub CloseRecordset() If Not (rs Is Nothing) Then rs.Close m_DataMember = "" End Sub

The Visual Basic documentation suggests that you return the same Recordset when multiple consumers ask for the same DataMember. For this reason, the class stores the DataMember argument in the m_DataMember private variable and reloads the text file only if strictly necessary. When I traced the source code, however, I found that the GetDataMember event is called just once with a nonempty string in the DataMember argument when the client program assigns the instance of the class to the DataSource property of the first bound control. Each time after that, the event receives an empty string.

The TextFileDataSource class on the companion CD includes many other features that I don't have room to describe here. Figure 18-2 shows the demonstration program, which loads two forms, a record-based view of a text file and a table-based view of the same file. Because the controls on both forms are bound to the same instance of the TextFileDataSource class, any time you move the record pointer or edit a field value in one form the contents of the other form are immediately updated. The class also exposes a Flush method, which writes the new values back to disk. This method is automatically invoked during the Class_Terminate event, so when the last form unloads and the data source object is released, the Flush method automatically updates the data file.

Figure 18-2. The demonstration program of the TextFileDataSource class can open different views of the same data file. If the views use the same instance of the class, they're automatically synchronized.

The TextFileDataSource class also offers an example of how you can add items to the DataMembers collection to inform data consumers about the available DataMembers items. The class module implements this feature in the Property Let FilePath procedure, where it loads the collection with all the data files in the specified directory:

Public Property Let FilePath(ByVal newValue As String) If newValue <> m_FilePath Then m_FilePath = newValue If m_FilePath <> "" And Right$(m_FilePath, 1) <> "\" Then m_FilePath = m_FilePath & "\" End If RefreshDataMembers End If End Property ' Rebuild the DataMembers collection. Private Sub RefreshDataMembers() Dim File As String DataMembers.Clear ' Load all the file names in the directory. File = Dir$(m_FilePath & "*" & DEFAULT_EXT) Do While Len(File) ' Drop the default extension. DataMembers.Add Left$(File, Len(File) - Len(DEFAULT_EXT)) File = Dir$() Loop End Sub

The TextFileDataSource class is bound to its consumers at run time. Therefore, there's no point in filling the DataMembers collection because the clients can't query this information. But this technique becomes useful when you're creating ActiveX controls that work as data sources because the list of all available DataMembers items appears right in the Properties windows of the controls that are bound to the ActiveX control.

Creating a custom Data control is simple because ActiveX controls can work as data sources exactly as classes and COM components can. So you can create a user interface that meets your needs, such as the one depicted in Figure 18-3, set the UserControl's DataSourceBehavior attribute to 1-vbDataSource, and add all the properties and methods that developers expect from a Data control, such as ConnectionString, RecordSource, EOFAction, and BOFAction. If you exactly duplicate the ADO Data interface, you might even be able to replace a standard ADO control with your custom Data control without changing a single line of code in client forms.

Figure 18-3. A custom Data control that includes buttons to add and delete records.

A custom Data control that connects to regular ADO sources doesn't need to manufacture an ADO recordset itself, as the data source classes I've shown you so far have. Instead, it internally creates an ADO Connection object and an ADO Recordset object based on the values of Public properties and then passes the Recordset to consumers in the GetDataMember event. The following code is a partial listing of the MyDataControl UserControl module. (The complete source code is on the companion CD.)

Private cn As ADODB.Connection, rs As ADODB.Recordset Private CnIsInvalid As Boolean, RsIsInvalid As Boolean Private Sub UserControl_GetDataMember(DataMember As String, Data As Object) On Error GoTo Error_Handler ' Re-create the connection if necessary. If cn Is Nothing Or CnIsInvalid Then ' If the Recordset and the connection are open, close them. CloseConnection ' Validate the ConnectionString property. If Trim$(m_ConnectionString) = "" Then Err.Raise 1001, , "ConnectionString can't be an empty string" Else ' Open the connection. Set cn = New ADODB.Connection If m_Provider <> "" Then cn.Provider = m_Provider cn.Open m_ConnectionString CnIsInvalid = False End If End If ' Re-create the Recordset if necessary. If rs Is Nothing Or RsIsInvalid Then Set rs = New ADODB.Recordset rs.CursorLocation = m_CursorLocation rs.Open RecordSource, cn, CursorType, LockType, CommandType rs.MoveFirst RsIsInvalid = False End If ' Return the Recordset to the data consumer. Set Data = rs Exit Sub Error_Handler: Err.Raise Err.Number, Ambient.DisplayName, Err.Description CloseConnection End Sub ' Close the Recordset and the connection in the correct way. Private Sub CloseRecordset() If Not rs Is Nothing Then If rs.State <> adStateClosed Then rs.Close Set rs = Nothing End If End Sub Private Sub CloseConnection() CloseRecordset If Not cn Is Nothing Then If cn.State <> adStateClosed Then cn.Close Set cn = Nothing End If End Sub

A custom Data control also differs from data source classes in that the code to navigate the Recordset is included in the UserControl module. In the MyDataControl module, the six navigational buttons belong to the cmdMove control array, which slightly simplifies their management:

Private Sub cmdMove_Click(Index As Integer) If rs Is Nothing Then Exit Sub ' Exit if the Recordset doesn't exist. Select Case Index Case 0 rs.MoveFirst Case 1 If rs.BOF Then Select Case BOFAction Case mdcBOFActionEnum.mdcBOFActionMoveFirst rs.MoveFirst Case mdcBOFActionEnum.mdcBOFActionBOF ' Do nothing. End Select Else rs.MovePrevious End If Case 2 If rs.EOF = False Then rs.MoveNext If rs.EOF = True Then Select Case EOFAction Case mdcEOFActionEnum.mdcEOFActionAddNew rs.AddNew Case mdcEOFActionEnum.mdcEOFActionMoveLast rs.MoveLast Case mdcEOFActionEnum.mdcEOFActionEOF ' Do nothing. End Select End If Case 3 rs.MoveLast Case 4 rs.AddNew Case 5 rs.Delete End Select End Sub

Each time the client assigns a value to a property that affects the Connection or the Recordset, the code in the MyDataControl module resets the cn or the rs variables to Nothing and sets the CnIsInvalid or RsIsInvalid variables to True so that in the next GetDataMember event the connection or the Recordset is correctly rebuilt:

Public Property Get ConnectionString() As String ConnectionString = m_ConnectionString End Property Public Property Let ConnectionString(ByVal New_ConnectionString As String) m_ConnectionString = New_ConnectionString PropertyChanged "ConnectionString" CnIsInvalid = True End Property

Remember to close the connection when the control is about to terminate:

Private Sub UserControl_Terminate() CloseConnection End Sub